const router = require('koa-router')()
const path = require('path')
const fs = require('fs')
const formidable = require('formidable')
const jwt = require('jsonwebtoken');

const courseImgPath = path.join(path.resolve(__dirname, '../..'), '/public/images/course_imgs');
const coursePath = path.join(path.resolve(__dirname, '../../'), '/public/images/course_imgs')
const avatarPath = path.join(path.resolve(__dirname, '../../'), '/public/images/avatars')
console.log(courseImgPath, 'at mycourse.js');

// 生成雪花ID的方法
const createSnow = function (ctx) {
    let snowFlake = ctx.snowFlake;
    let snowId = snowFlake.createSnow(5);
    return snowId;
}
// 将标准时间转换为YYYY-MM-DD HH:MM:SS类型的
const formTime = (time) => {
    let newtime = new Date(time);
    // console.log(new Date(time), "要进行转换的时间");
    const year = newtime.getFullYear();
    const month = String(newtime.getMonth() + 1).padStart(2, "0");
    const day = String(newtime.getDate()).padStart(2, "0");
    const hours = String(newtime.getHours()).padStart(2, "0");
    const minutes = String(newtime.getMinutes()).padStart(2, "0");
    const seconds = String(newtime.getSeconds()).padStart(2, "0");

    const formattedDate = `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
    return formattedDate;
}

router.prefix('/mycourse')

// 通过用户的身份查找用户的课程
router.post('/gitcoursebyid', async (ctx, next) => {
    let { currentPage, id, auth } = ctx.request.body

    if (auth == 'teacher_per') {
        let sql = 'select * from course where teacher_id=?'
        let res = await ctx.db.EXCUTE(sql, [id])
        console.log(res);
        ctx.body = { code: 200, msg: "成功查询", list: res.slice((currentPage - 1) * 8, currentPage * 8), total: res.length }
    }
    if (auth == 'student_per') {
        // 联表查询
        let lbsql = 'select * from course inner join student_course_relation on course.course_id=student_course_relation.course_id where student_course_relation.student_id=?'
        let lbres = await ctx.db.EXCUTE(lbsql, [id])
        ctx.body = { code: 200, msg: "成功查询", list: lbres.slice((currentPage - 1) * 8, currentPage * 8), total: lbres.length }
    }

})
// 添加课程时，查询全部课程
router.post('/gitcourse', async (ctx, next) => {
    let { currentPage, id, institute_id } = ctx.request.body
    // 联表查询--查出已经选的课程
    let lbsql = 'select * from course inner join student_course_relation on course.course_id=student_course_relation.course_id where student_course_relation.student_id=?'
    let lbres = await ctx.db.EXCUTE(lbsql, [id])
    let list = lbres.map(item => {
        return { ...item, apply: true }
    })
    console.log('lbres', lbres);
    // 联表查询--查出未选的课程
    let lbsql2 = 'select * from course where course_id not in (select course_id from student_course_relation where student_id=?) and institute_id=? and course.apply_end_date>? and course.apply_start_date<?'
    let lbres2 = await ctx.db.EXCUTE(lbsql2, [id, institute_id, new Date(), new Date()])
    let arr = lbres2.map(item => {
        return { ...item, apply: false }
    })
    console.log('lbres2', lbres2);

    ctx.body = { code: 200, msg: "成功查询", list: [...arr, ...list].slice((currentPage - 1) * 8, currentPage * 8), total: [...list, ...arr].length }
})
// 查找年级
router.post('/getgrade', async (ctx, next) => {
    let { institute_id } = ctx.request.body
    let sql = 'select * from grade where institute_id=?'
    let res = await ctx.db.EXCUTE(sql, [institute_id])
    console.log(res);

    ctx.body = { code: 200, msg: "成功查询", list: res }
})
// 搜索
router.post('/findcourse', async (ctx, next) => {
    let { keyword, currentPage, grade_id, id } = ctx.request.body
    console.log(keyword, id);
    let msgObj = {}

    if (grade_id == '') {
        // 联表查询--查出已经选的课程
        let lbsql = `select * from course inner join student_course_relation on course.course_id=student_course_relation.course_id where student_course_relation.student_id=? and course.course_name LIKE '%${keyword}%'`
        let lbres = await ctx.db.EXCUTE(lbsql, [id])
        let list = lbres.map(item => {
            return { ...item, apply: true }
        })
        console.log('lbres', lbres);
        // 联表查询--查出未选的课程
        let lbsql2 = `select * from course where course_id not in (select course_id from student_course_relation where student_id=?) and course.course_name LIKE '%${keyword}%'`
        let lbres2 = await ctx.db.EXCUTE(lbsql2, [id])
        let arr = lbres2.map(item => {
            return { ...item, apply: false }
        })
        console.log('lbres2', lbres2);
        msgObj = { List: [...list, ...arr].slice((currentPage - 1) * 8, currentPage * 8), total: [...list, ...arr].length }
    } else {
        // 联表查询--查出已经选的课程
        let lbsql = `select * from course inner join student_course_relation on course.course_id=student_course_relation.course_id where student_course_relation.student_id=? and course.grade_id=? and course.course_name LIKE '%${keyword}%'`
        let lbres = await ctx.db.EXCUTE(lbsql, [id, grade_id])
        let list = lbres.map(item => {
            return { ...item, apply: true }
        })
        console.log('lbres', lbres);
        // 联表查询--查出未选的课程
        let lbsql2 = `select * from course where course_id not in (select course_id from student_course_relation where student_id=?) and course.grade_id=? and course.course_name LIKE '%${keyword}%'`
        let lbres2 = await ctx.db.EXCUTE(lbsql2, [id, grade_id])
        let arr = lbres2.map(item => {
            return { ...item, apply: false }
        })
        console.log('lbres2', lbres2);
        msgObj = { List: [...list, ...arr].slice((currentPage - 1) * 8, currentPage * 8), total: [...list, ...arr].length }
    }

    if (msgObj.total == 0) {
        msgObj = { ...msgObj, code: 204, msg: '没有找到相关信息' }
    }
    else {
        msgObj = { ...msgObj, code: 200, msg: '查询成功!' }
    }

    ctx.body = msgObj
})
// 搜索老师课程
router.post('/tfindcourse', async (ctx, next) => {
    let { keyword, currentPage, id } = ctx.request.body
    console.log(keyword, id);
    let msgObj = {}

    // 联表查询--查出已经选的课程
    let lbsql = `select * from course where teacher_id=? and course_name LIKE '%${keyword}%'`
    let lbres = await ctx.db.EXCUTE(lbsql, [id])
    console.log('lbres', lbres);

    msgObj = { List: lbres.slice((currentPage - 1) * 8, currentPage * 8), total: lbres.length }

    if (msgObj.total == 0) {
        msgObj = { ...msgObj, code: 204, msg: '没有找到相关信息' }
    }
    else {
        msgObj = { ...msgObj, code: 200, msg: '查询成功!' }
    }

    ctx.body = msgObj
})
// 通过年级搜索课程
router.post('/selectcoursebygradeid', async (ctx, next) => {
    let { grade_id, currentPage, id } = ctx.request.body
    let msgObj = {}

    // 联表查询--查出已经选的课程
    let lbsql = 'select * from course inner join student_course_relation on course.course_id=student_course_relation.course_id where student_course_relation.student_id=? and course.grade_id=?'
    let lbres = await ctx.db.EXCUTE(lbsql, [id, grade_id])
    let list = lbres.map(item => {
        return { ...item, apply: true }
    })
    console.log('lbres', lbres);
    // 联表查询--查出未选的课程
    let lbsql2 = 'select * from course where course_id not in (select course_id from student_course_relation where student_id=?) and course.grade_id=?'
    let lbres2 = await ctx.db.EXCUTE(lbsql2, [id, grade_id])
    let arr = lbres2.map(item => {
        return { ...item, apply: false }
    })
    console.log('lbres2', lbres2);

    let total = [...list, ...arr].length;
    if (total == 0) {
        msgObj = { code: 200, msg: '没有找到相关信息', total: total, List: [...arr, ...list].slice((currentPage - 1) * 8, currentPage * 8) }
    }
    else {
        msgObj = { code: 200, msg: '查询成功!', total: total, List: [...arr, ...list].slice((currentPage - 1) * 8, currentPage * 8) }
    }

    ctx.body = msgObj
})

// 报名
router.post('/apply', async (ctx, next) => {
    let { course_id, student_id, course_num } = ctx.request.body
    let msgObj = {}

    let verifySql = 'select * from student_course_relation where course_id=? and student_id=?'
    let verifyRes = await ctx.db.EXCUTE(verifySql, [course_id, student_id])
    // 该学生没有报名该课程
    if (verifyRes.length == 0) {
        let numSql = 'select * from student_course_relation where course_id=?'
        let numRes = await ctx.db.EXCUTE(numSql, [course_id])
        if (numRes.length < course_num) {
            let sql = 'insert into student_course_relation(course_id,student_id) values(?,?)'
            let result = await ctx.db.EXCUTE(sql, [course_id, student_id])
            // console.log(result);
            msgObj = { code: 200, msg: '报名成功!' }
        }
        else {
            msgObj = { code: 204, msg: '课程人员已满!' }
        }
    }
    else {
        msgObj = { code: 201, msg: '您已经报名了该课程!' }
    }

    ctx.body = msgObj
})
// 退课
router.post('/drop', async (ctx, next) => {
    let { course_id, student_id } = ctx.request.body

    let sql = 'delete from student_course_relation where course_id=? and student_id=?'
    let res = await ctx.db.EXCUTE(sql, [course_id, student_id])
    console.log(res);
    if (res.affectedRows != 0) {
        ctx.body = { code: 200, msg: '退课成功!' }
    } else {
        ctx.body = { code: 1722, msg: 'SQL Server error' }
    }
})
// 退课
// router.post('/drop', async (ctx, next) => {
//     let { course_id, student_id } = ctx.request.body

//     let sql = 'select student_assignment_id from student_submit_assignment where assignment_id IN (select assignment_id from assignment where course_id=?) and student_id=?'
//     let res = await ctx.db.EXCUTE(sql, [course_id, student_id])
//     if (res.length == 0) {
//         let sql = 'delete from student_course_relation where course_id=? and student_id=?'
//         let res = await ctx.db.EXCUTE(sql, [course_id, student_id])
//         console.log(res);
//         if (res.affectedRows != 0) {
//             ctx.body = { code: 200, msg: '退课成功!' }
//         } else {
//             ctx.body = { code: 01722, msg: 'SQL Server error' }
//         }
//     } else {
//         let sql2 = 'delete from student_submit_assignment where assignment_id IN (select assignment_id from assignment where course_id=?) and student_id=?'
//         let res2 = await ctx.db.EXCUTE(sql2, [course_id, student_id])
//         if (res2.affectedRows != 0) {
//             let sql = 'delete from student_course_relation where course_id=? and student_id=?'
//             let res = await ctx.db.EXCUTE(sql, [course_id, student_id])
//             console.log(res);
//             if (res.affectedRows != 0) {
//                 ctx.body = { code: 200, msg: '退课成功!' }
//             } else {
//                 ctx.body = { code: 01722, msg: 'SQL Server error' }
//             }
//         }
//     }
// })

// 课程详情
router.post('/getdetail', async (ctx, next) => {
    let { institute_id, grade_id, course_id, teacher_id } = ctx.request.body
    let sql = 'select course.course_id,course.course_name,course.course_desc,course.course_image,course.course_num,course.course_credit,course.apply_start_date,course.apply_end_date,course.start_date,course.end_date,grade.grade_id,grade.grade_name,teacher.teacher_id,teacher.teacher_name,teacher.avatar,teacher.signature,teacher.teacher_experience,institute.institute_id,institute.institute_name from course join institute on institute.institute_id=course.institute_id join grade on grade.grade_id=course.grade_id join teacher on teacher.teacher_id=course.teacher_id where course.course_id=?'
    let res = await ctx.db.EXCUTE(sql, [course_id])
    let numSql = 'select * from student_course_relation where course_id=?'
    let numRes = await ctx.db.EXCUTE(numSql, [course_id])
    ctx.body = { code: 200, data: res, total: numRes.length }
})
// 上传课程图片
router.post('/uploadimg/:course_id', async (ctx, next) => {
    const { course_id } = ctx.params
    console.log(course_id);
    let sql = 'select course_image from course where course_id=?'
    let res = await ctx.db.EXCUTE(sql, [course_id])
    if (!res[0].course_image) {
        ctx.body = await CheckUpload(ctx, coursePath, course_id, 'update course set course_image=? where course_id=?')
    } else {
        let filePath = path.join(coursePath, res[0].course_image);
        if (fs.existsSync(filePath)) {
            // 删除文件
            fs.unlinkSync(filePath);
        }
        ctx.body = await CheckUpload(ctx, coursePath, course_id, 'update course set course_image=? where course_id=?')
    }
})
const CheckUpload = (ctx, Path, id, sql) => {
    const form = formidable({
        multipart: true,
        uploadDir: Path,
        keepExtensions: true,
        allowEmptyFiles: false
    });
    return new Promise((resolve, reject) => {
        form.parse(ctx.req, async (err, fields, files) => {//ctx.req原生的node的http请求模块；ctx.require.body是经过koa二次封装之后的
            if (err) {
                reject(err);
                return;
            }
            let keys = Object.keys(files);//获取files对象中的属性名[formData传入的图片]
            keys?.map(async (key) => {
                console.log(files[key]);
                let { size, filepath, newFilename, originalFilename, mimetype } = files[key];
                console.log(sql);
                let Filename = Date.now() + '.' + originalFilename.split('.')[1];
                let result = await ctx.db.EXCUTE(sql, [Filename, id]);
                console.log('uploadRes:', result);

                console.log(Filename, "新的文件名称");
                console.log(originalFilename, "原先的文件名称");
                // 将文件重命名为原本的文件名
                fs.rename(path.join(Path, `/${newFilename}`), path.join(Path, `/${Filename}`),
                    (err) => {
                        if (err) {
                            console.log(err);
                        } else {
                            console.log('file is reset!');
                        }
                    })
            })
            resolve({ code: 200, fields, files });
        });
    });
}
// 修改课程总人数
router.post('/changenum', async (ctx, next) => {
    let { course_id, course_num } = ctx.request.body
    let sql = 'update course set course_num=? where course_id=?'
    let res = await ctx.db.EXCUTE(sql, [course_num, course_id])
    ctx.body = { code: 200, msg: '修改成功!' }
})
// 修改用户信息后重新生成加密信息
router.post('/submit', async (ctx, next) => {
    let { user_id, user_auth, user_name } = ctx.request.body;
    const user = { username: user_name };
    // 从全局变量中拿出secretKey
    // 生成 token
    const token = jwt.sign(user, ctx.config.secretKey, { expiresIn: '3h' });
    const refreshToken = jwt.sign({ auth: true }, ctx.config.secretKey, { expiresIn: '7d' });
    let user_info;
    // 通过身份来查询其对应的信息
    if (user_auth === "student_per") {
        let sql = "select student.user_id, student.user_name, student.student_name, student.signature, student.class_id, student.grade_id, student.institute_id, student.student_id, student.avatar, class.class_name, grade.grade_name, institute.institute_name from student join class on student.class_id = class.class_id join grade on student.grade_id = grade.grade_id join institute on student.institute_id = institute.institute_id where student.user_id = ?";
        let res = await ctx.db.EXCUTE(sql, [user_id]);
        user_info = res[0]
        // console.log(res);
    } else if (user_auth === 'teacher_per') {
        let sql = "select teacher.user_id, teacher.teacher_name, teacher.user_name, teacher.signature, teacher.institute_id, teacher.teacher_id, teacher.avatar, teacher.teacher_experience, institute.institute_name from teacher join institute on teacher.institute_id = institute.institute_id where teacher.user_id = ?";
        let res = await ctx.db.EXCUTE(sql, [user_id]);
        user_info = res[0];
    }
    user_info = { ...user_info, user_auth: user_auth }
    console.log(JSON.stringify(user_info));
    user_info = ctx.Aes.aesEncrypt(ctx, JSON.stringify(user_info))
    ctx.body = { code: 200, msg: "用户信息已更新", token, refreshToken, user_info: user_info }

    // ctx.cookies.set('token', token, { httpOnly: true, domain: "localhost", path: '/', overwrite: true });
})
// 获取课程全部成员信息
router.post('/getstubycourse', async (ctx, next) => {
    let { course_id, currentPage } = ctx.request.body
    let sql = 'select student.user_id, student.avatar, student.student_name, student.student_id, institute.institute_id, institute.institute_name, grade.grade_id, grade.grade_name, class.class_id, class.class_name from student join institute on student.institute_id = institute.institute_id join grade on student.grade_id = grade.grade_id join class on student.class_id = class.class_id join (select student_id from student_course_relation where course_id = ?) as stuid on student.student_id = stuid.student_id'
    let res = await ctx.db.EXCUTE(sql, [course_id])
    console.log(res);
    ctx.body = { code: 200, data: res.slice((currentPage - 1) * 5, currentPage * 5), total: res.length }
})
// 获取对应班级未选该门课程的学生列表
router.post('/getstudrawlist', async (ctx, next) => {
    let { class_id, course_id, grade_id } = ctx.request.body
    let clasql = 'select class.class_id, class.class_name from class where grade_id = ?'
    let clares = await ctx.db.EXCUTE(clasql, [grade_id])
    let sql = 'select student.user_id, student.avatar, student.student_name, student.student_id, institute.institute_id, institute.institute_name, grade.grade_id, grade.grade_name, class.class_id, class.class_name from student join institute on student.institute_id = institute.institute_id join grade on student.grade_id = grade.grade_id join class on student.class_id = class.class_id join (select student_id from student where student_id NOT IN (select student_id from student_course_relation where course_id = ?)) as stuid on student.student_id = stuid.student_id where student.class_id = ?'
    if (clares.length == 0) {
        ctx.body = { code: 203, msg: '该年级还未开设班级' }
    } else {
        let res
        if (class_id == '') {
            res = await ctx.db.EXCUTE(sql, [course_id, clares[0].class_id])
        } else {
            res = await ctx.db.EXCUTE(sql, [course_id, class_id])
        }
        ctx.body = { code: 200, studata: res, cladata: clares }
    }
})
// 确认添加学生
router.post('/inviteconfirm', async (ctx, next) => {
    let { list, course_id, course_name, sender } = ctx.request.body
    console.log(course_name);
    let sql = 'insert into student_course_relation (course_id, student_id) values '
    let msql = 'insert into message (message_id, sender, recipient, content, status, create_time, update_time, type) values '
    let stuArr = [];
    let mArr = [];
    let msgObj = {}
    let msgArr = [];
    console.log(list)
    for (let i = 0; i < list.length; i++) {
        let datacenterId = i + 1; // 根据需要设置不同的数据中心ID
        let workerId = i + 1; // 根据需要设置不同的工作器ID
        let id = ctx.snowFlake.createSnow(datacenterId, workerId);
        while (msgArr.includes(id)) {
          id = ctx.snowFlake.createSnow(datacenterId, workerId);
        }
        msgArr.push(id);
        // console.log(id, "雪花ID");
      }
    list.forEach((item,index) => {
        let val = `('${course_id}', '${item.student_id}')`
        stuArr.push(val)
        let mval = `('${msgArr[index]}', '${sender}', '${item.user_id}', '邀请你加入${course_name}课程', 'unread', '${formTime(new Date())}', '${formTime(new Date())}', 'info')`
        mArr.push(mval)
    })
    if (stuArr.length > 0 && mArr.length > 0) {
        sql += stuArr.join(', ')
        let res = await ctx.db.EXCUTE(sql, [])
        msql += mArr.join(', ')
        let mres = await ctx.db.EXCUTE(msql, [])
        console.log(res);
        console.log(mres);
        if (res.affectedRows != 0 && mres.affectedRows != 0) {
            msgObj = { code: 200 }
        } else {
            msgObj = { code: 204 }
        }
        // msgObj = {code: 200}
    }
    ctx.body = msgObj
})


/**
 * 头像接口
 */
// 修改密码
router.post('/changepwd', async (ctx, next) => {
    let { oldPassword, newPassword, confirmPassword, user_id } = ctx.request.body
    let Old = ctx.Rsa.decrypt(ctx, oldPassword)
    let New = ctx.Rsa.decrypt(ctx, newPassword)
    let Con = ctx.Rsa.decrypt(ctx, confirmPassword)
    let sql = 'select user_password from user where user_id=?'
    let res = await ctx.db.EXCUTE(sql, [user_id])
    console.log(res[0].user_password, '密码');
    if (ctx.Rsa.decrypt(ctx, oldPassword) == ctx.Rsa.decrypt(ctx, res[0].user_password)) {
        let sql = 'update user set user_password=? where user_id=?'
        let res = await ctx.db.EXCUTE(sql, [ctx.Rsa.encrypt(ctx, New), user_id])
        if (res.affectedRows != 0) {
            ctx.body = { code: 200, msg: '修改成功', data: { oldPassword: Old, newPassword: New, confirmPassword: Con } }
        } else {
            ctx.body = { code: 1722, msg: 'SQL Server error' }
        }
    } else {
        ctx.body = { code: 203, msg: '原密码错误', data: { oldPassword: Old, newPassword: New, confirmPassword: Con } }
    }
})

// 修改用户信息
router.post('/changeinfo', async (ctx, next) => {
    let { user_name, signature, user_id, user_auth } = ctx.request.body
    let msgObj = {}
    let hasSql = 'select user_name from user where user_name=? and user_id != ?'
    let hasRes = await ctx.db.EXCUTE(hasSql, [user_name, user_id])
    console.log(hasRes);
    if (hasRes.length == 0) {
        let sql = 'update user set user_name=? where user_id=?'
        let res = await ctx.db.EXCUTE(sql, [user_name, user_id])
        console.log(res);
        if (res.affectedRows != 0) {//user表修改成功
            if (user_auth == 'teacher_per') {
                let sql = 'update teacher set signature=?,user_name=? where user_id=?'
                let res = await ctx.db.EXCUTE(sql, [signature, user_name, user_id])
                console.log(res);
                if (res.affectedRows != 0) {//teacher表修改成功
                    let backsql = 'select signature,user_name from teacher where user_id=?'
                    let backres = await ctx.db.EXCUTE(backsql, [user_id])
                    console.log(backres, '查询结果');
                    msgObj = backres[0]
                }
            }
            if (user_auth == 'student_per') {
                let sql = 'update student set signature=?,user_name=? where user_id=?'
                let res = await ctx.db.EXCUTE(sql, [signature, user_name, user_id])
                console.log(res);
                if (res.affectedRows != 0) {//student表修改成功
                    let backsql = 'select signature,user_name from student where user_id=?'
                    let backres = await ctx.db.EXCUTE(backsql, [user_id])
                    msgObj = backres[0]
                }
            }
        }
        ctx.body = { code: 200, msg: '修改成功', data: msgObj }
    } else {
        ctx.body = { code: 201, msg: '该用户名已存在，请换一个试试' }
    }
})
// 上传用户头像
router.post('/uploadavatar', async (ctx, next) => {
    const user_id = ctx.query.user_id
    const user_auth = ctx.query.user_auth
    console.log(user_auth);
    if (user_auth == 'student_per') {
        let sql = 'select avatar from student where user_id=?'
        let res = await ctx.db.EXCUTE(sql, [user_id])
        console.log(res);
        if (!res[0].avatar) {
            ctx.body = await CheckUpload(ctx, avatarPath, user_id, 'update student set avatar=? where user_id=?')
        } else {
            let filePath = path.join(avatarPath, res[0].avatar);
            if (fs.existsSync(filePath)) {
                // 删除文件
                fs.unlinkSync(filePath);
            }
            ctx.body = await CheckUpload(ctx, avatarPath, user_id, 'update student set avatar=? where user_id=?')
        }
    }
    if (user_auth == 'teacher_per') {
        let sql = 'select avatar from teacher where user_id=?'
        let res = await ctx.db.EXCUTE(sql, [user_id])
        if (!res[0].avatar) {
            ctx.body = await CheckUpload(ctx, avatarPath, user_id, 'update teacher set avatar=? where user_id=?')
        } else {
            let filePath = path.join(avatarPath, res[0].avatar);
            if (fs.existsSync(filePath)) {
                // 删除文件
                fs.unlinkSync(filePath);
            }
            ctx.body = await CheckUpload(ctx, avatarPath, user_id, 'update teacher set avatar=? where user_id=?')
        }
    }
})

module.exports = router